<?php

// +----------------------------------------------------------------------
// | Author: 发仔 <i@fazi.me>
// +----------------------------------------------------------------------

namespace app\admin\controller;

use think\Db;
use think\Cache;

class Feiyong extends Base {

    public function index() {
        $P = input('param.');
        $where = [];
        $where['name'] = ['LIKE', "%{$P['name']}%"];
        $w['start_date'] = ['between', [$P['start_date'], $P['end_date']]];
        if (!empty($P['name'])) {
            $wxquery = Db::table('p_maintenance_record')->field('car_id,sum(price)as wxp')->where($w)->group('car_id')->fetchSql(true)->select();
            $njquery = Db::table('p_mot_record')->field('car_id,sum(price)as njp')->where($w)->group('car_id')->fetchSql(true)->select();
            $bxquery = Db::table('p_car_insure')->field('car_id,sum(policy_charge)as bxp')->where($w)->group('car_id')->fetchSql(true)->select();
            $yfquery = Db::table('p_refuel_record')->field('car_id,sum(price)as yfp')->where($w)->group('car_id')->fetchSql(true)->select();
            $car = Db::name('car')->alias('a')->join('car_rental b', 'a.car_rental_id = b.car_rental_id', 'LEFT')->
                            join("($wxquery) wxquery", 'wxquery.car_id=a.car_id', 'LEFT')->
                            join("($njquery) njquery", 'njquery.car_id=a.car_id', 'LEFT')->
                            join("($bxquery) bxquery", 'bxquery.car_id=a.car_id', 'LEFT')->
                            join("($yfquery) yfquery", 'yfquery.car_id=a.car_id', 'LEFT')->
                            where($where)->field('a.number,a.car_id,wxp,njp,bxp,yfp')->group('car_id')->select();
        }

        $this->assign('car', $car);
        return $this->out();
    }

    
}
